package buytogether.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.annotation.Resource;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import javax.sql.DataSource;
import javax.sql.rowset.CachedRowSet;

@ManagedBean
@RequestScoped
public class CityBean {
    private int id;
    private String name;
    
    @Resource(name="jdbc/BuyTogether")
    DataSource dataSource;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
    
    public ResultSet getCities() throws SQLException
    {
        Connection connection = getConnection();
        try
        {
            PreparedStatement getCities = 
                    connection.prepareStatement
                    ("SELECT CITY_ID, CITY_NAME FROM CITIES ORDER BY CITY_NAME");
            
            CachedRowSet rowSet = new com.sun.rowset.CachedRowSetImpl();
            rowSet.populate(getCities.executeQuery());
            return rowSet;
        }
        finally
        {
            connection.close();
        }
    }
    
    public String save() throws SQLException
    {
        Connection connection = getConnection();
        try
        {
            PreparedStatement addCity = 
                    connection.prepareStatement
                    ("INSERT INTO CITIES (CITY_ID, CITY_NAME) VALUES(?, ?)" );
            addCity.setInt(1, getId());
            addCity.setString(2, getName());
            
            addCity.executeUpdate();
            return "";
        }
        finally
            {
                connection.close();                
            }
    }

    private Connection getConnection() throws SQLException
    {
        if(dataSource == null)
            throw new SQLException("חיבור לבסיס הנתונים נכשל");
        Connection connection = dataSource.getConnection();
        if(connection == null)
            throw new SQLException("חיבור לבסיס הנתונים נכשל");
        return connection;
        
    }
}
    
    

